{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "disturbed-prayer",
   "metadata": {},
   "source": [
    "# 通用模板自动填充库应用演示\n",
    "\n",
    "> * 准备工作\n",
    ">   - 导入包\n",
    ">   - 模板文件\n",
    ">   - 填充定位\n",
    ">   - 填充内容文件\n",
    "> * 应用操作\n",
    ">   - 填充内容分割\n",
    ">     - 提取一对一映射表\n",
    ">     - 提取一对多映射表\n",
    ">     - 打包\n",
    ">   - 创建 Exsheet 对象\n",
    ">   - 读入模板\n",
    ">   - 准备空白 Excel 工作表\n",
    ">   - 在工作表中逐一放入模板块\n",
    ">     - 拆包填充内容\n",
    ">     - 新块\n",
    ">     - 填充内容\n",
    ">     - 放入 Excel 的 Sheet 表\n",
    ">   - 对 Excel 的 Sheet 进行布局设置\n",
    ">   - 保存为 Excel 文件\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "distinct-mixture",
   "metadata": {},
   "source": [
    "1. 准备工作\n",
    "<br>1.1 导入包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "atlantic-lebanon",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from openpyxl import Workbook\n",
    "import fillpat as fp\n",
    "import fin"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "legislative-delaware",
   "metadata": {},
   "source": [
    "    1.2 模板文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "technological-stationery",
   "metadata": {},
   "outputs": [],
   "source": [
    "pat = r'.\\datas\\费用报销单.xlsx'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "compatible-pillow",
   "metadata": {},
   "source": [
    "    1.3 填充定位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "martial-gnome",
   "metadata": {},
   "outputs": [],
   "source": [
    "oto_loc = {\n",
    "    'C3' :'公司名称',\n",
    "    'M3' :'请款部门',\n",
    "    'W3' :'填单日期',\n",
    "    'F11':'小写合计',\n",
    "    'M11':'大写合计',\n",
    "    'W11':'附件数量',\n",
    "    'E12':'是转工资',\n",
    "    'G12':'否转工资',\n",
    "    'K12':'原借支',\n",
    "    'O12':'现报销',\n",
    "    'W12':'剩余交回',\n",
    "    'K13':'收款账号',\n",
    "    'U13':'开户银行'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "selected-hostel",
   "metadata": {},
   "outputs": [],
   "source": [
    "mtm_loc = {\n",
    "    '6:10':'行范围',\n",
    "    'B':'事由或品名',\n",
    "    'I':'单位',\n",
    "    'J':'数量',\n",
    "    'L':'单价',\n",
    "    'M':'币种',\n",
    "    'N':'金额',\n",
    "    'W':'备注'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "welcome-reggae",
   "metadata": {},
   "source": [
    "    1.4 填充内容文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "promising-regulation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>公司名称</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>事由或品名</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>币种</th>\n",
       "      <th>金额</th>\n",
       "      <th>...</th>\n",
       "      <th>小写合计</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>是转工资</th>\n",
       "      <th>否转工资</th>\n",
       "      <th>原借支</th>\n",
       "      <th>现报销</th>\n",
       "      <th>剩余交回</th>\n",
       "      <th>受款人</th>\n",
       "      <th>收款账号</th>\n",
       "      <th>开户银行</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>订书机</td>\n",
       "      <td>个</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>人民币</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>打印机</td>\n",
       "      <td>台</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4000</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>鼠标</td>\n",
       "      <td>个</td>\n",
       "      <td>10</td>\n",
       "      <td>75</td>\n",
       "      <td>人民币</td>\n",
       "      <td>750</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>笔记本</td>\n",
       "      <td>本</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>人民币</td>\n",
       "      <td>60</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>回形针</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>张三</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>试验</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>A</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>B</td>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>C</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>人民币</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>李四</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID 公司名称 请款部门       填单日期 事由或品名 单位  数量    单价   币种    金额  ... 小写合计  附件数量  \\\n",
       "0   1  某公司  财务部 2022-03-29   订书机  个   5    10  人民币    50  ...  NaN     7   \n",
       "1   1  某公司  财务部 2022-03-29   打印机  台   2  2000  人民币  4000  ...  NaN     7   \n",
       "2   1  某公司  财务部 2022-03-29    鼠标  个  10    75  人民币   750  ...  NaN     7   \n",
       "3   1  某公司  财务部 2022-03-29   笔记本  本   3    20  人民币    60  ...  NaN     7   \n",
       "4   1  某公司  财务部 2022-03-29   回形针  盒   3     2  人民币     6  ...  NaN     7   \n",
       "5   2  某公司  财务部 2022-03-29    试验  盒   3     2  人民币     6  ...  NaN     1   \n",
       "6   2  某公司  财务部 2022-03-29     A  a   1     1  人民币     1  ...  NaN     1   \n",
       "7   2  某公司  财务部 2022-03-29     B  b   2     2  人民币     4  ...  NaN     1   \n",
       "8   2  某公司  财务部 2022-03-29     C  c   3     3  人民币     9  ...  NaN     1   \n",
       "\n",
       "   是转工资  否转工资   原借支 现报销  剩余交回  受款人                     收款账号  开户银行  \n",
       "0     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "1     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "2     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "3     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "4     √   NaN  4000 NaN   NaN   张三  2220 1298 2983 2981 337  建设银行  \n",
       "5   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "6   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "7   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "8   NaN     √     0 NaN   NaN   李四  2220 1298 2983 2981 445  建设银行  \n",
       "\n",
       "[9 rows x 22 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fil = pd.read_excel(r'.\\datas\\费用报销记录.xlsx')\n",
    "fil"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "assisted-directive",
   "metadata": {},
   "source": [
    "2. 应用操作：\n",
    "<br>2.1 填充内容分割\n",
    "   <br>2.1.1 提取一对一映射表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "weird-token",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ID',\n",
       " '公司名称',\n",
       " '请款部门',\n",
       " '填单日期',\n",
       " '小写合计',\n",
       " '大写合计',\n",
       " '附件数量',\n",
       " '是转工资',\n",
       " '否转工资',\n",
       " '原借支',\n",
       " '现报销',\n",
       " '剩余交回',\n",
       " '收款账号',\n",
       " '开户银行']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oto_col = list(oto_loc.values())\n",
    "oto_col = ['ID'] + oto_col\n",
    "oto_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "overall-wages",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>公司名称</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>小写合计</th>\n",
       "      <th>大写合计</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>是转工资</th>\n",
       "      <th>否转工资</th>\n",
       "      <th>原借支</th>\n",
       "      <th>现报销</th>\n",
       "      <th>剩余交回</th>\n",
       "      <th>收款账号</th>\n",
       "      <th>开户银行</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   公司名称 请款部门       填单日期  小写合计  大写合计  附件数量 是转工资 否转工资   原借支  现报销  剩余交回  \\\n",
       "ID                                                                     \n",
       "1   某公司  财务部 2022-03-29   NaN   NaN     7    √  NaN  4000  NaN   NaN   \n",
       "2   某公司  财务部 2022-03-29   NaN   NaN     1  NaN    √     0  NaN   NaN   \n",
       "\n",
       "                       收款账号  开户银行  \n",
       "ID                                 \n",
       "1   2220 1298 2983 2981 337  建设银行  \n",
       "2   2220 1298 2983 2981 445  建设银行  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adatas = fil[oto_col].drop_duplicates().set_index('ID')\n",
    "adatas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "pointed-success",
   "metadata": {},
   "source": [
    "**完善合计数**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "israeli-dragon",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>公司名称</th>\n",
       "      <th>请款部门</th>\n",
       "      <th>填单日期</th>\n",
       "      <th>小写合计</th>\n",
       "      <th>大写合计</th>\n",
       "      <th>附件数量</th>\n",
       "      <th>是转工资</th>\n",
       "      <th>否转工资</th>\n",
       "      <th>原借支</th>\n",
       "      <th>现报销</th>\n",
       "      <th>剩余交回</th>\n",
       "      <th>收款账号</th>\n",
       "      <th>开户银行</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>4866</td>\n",
       "      <td>肆仟捌佰陆拾陆元整</td>\n",
       "      <td>7</td>\n",
       "      <td>√</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 337</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>某公司</td>\n",
       "      <td>财务部</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>20</td>\n",
       "      <td>贰拾元整</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>√</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2220 1298 2983 2981 445</td>\n",
       "      <td>建设银行</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   公司名称 请款部门       填单日期  小写合计       大写合计  附件数量 是转工资 否转工资   原借支  现报销  剩余交回  \\\n",
       "ID                                                                          \n",
       "1   某公司  财务部 2022-03-29  4866  肆仟捌佰陆拾陆元整     7    √  NaN  4000  NaN   NaN   \n",
       "2   某公司  财务部 2022-03-29    20       贰拾元整     1  NaN    √     0  NaN   NaN   \n",
       "\n",
       "                       收款账号  开户银行  \n",
       "ID                                 \n",
       "1   2220 1298 2983 2981 337  建设银行  \n",
       "2   2220 1298 2983 2981 445  建设银行  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adatas['小写合计'] = fil.groupby('ID')['金额'].sum()\n",
    "adatas['大写合计'] = adatas.小写合计.apply(fin.amount_converter)\n",
    "adatas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "directed-fireplace",
   "metadata": {},
   "source": [
    "    2.1.2 提取一对多映射表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "conventional-surprise",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ID', '事由或品名', '单位', '数量', '单价', '币种', '金额', '备注']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mtm_col = ['ID'] + list(mtm_loc.values())[1:]\n",
    "mtm_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "found-chess",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>事由或品名</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>币种</th>\n",
       "      <th>金额</th>\n",
       "      <th>备注</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>订书机</td>\n",
       "      <td>个</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>人民币</td>\n",
       "      <td>50</td>\n",
       "      <td>老板用</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>打印机</td>\n",
       "      <td>台</td>\n",
       "      <td>2</td>\n",
       "      <td>2000</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>鼠标</td>\n",
       "      <td>个</td>\n",
       "      <td>10</td>\n",
       "      <td>75</td>\n",
       "      <td>人民币</td>\n",
       "      <td>750</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>笔记本</td>\n",
       "      <td>本</td>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>人民币</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>回形针</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>试验</td>\n",
       "      <td>盒</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>A</td>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>人民币</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>B</td>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>人民币</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>C</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>人民币</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID 事由或品名 单位  数量    单价   币种    金额   备注\n",
       "0   1   订书机  个   5    10  人民币    50  老板用\n",
       "1   1   打印机  台   2  2000  人民币  4000  NaN\n",
       "2   1    鼠标  个  10    75  人民币   750  NaN\n",
       "3   1   笔记本  本   3    20  人民币    60  NaN\n",
       "4   1   回形针  盒   3     2  人民币     6  NaN\n",
       "5   2    试验  盒   3     2  人民币     6  NaN\n",
       "6   2     A  a   1     1  人民币     1  NaN\n",
       "7   2     B  b   2     2  人民币     4  NaN\n",
       "8   2     C  c   3     3  人民币     9  NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datass = fil[mtm_col]\n",
    "datass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "careful-biography",
   "metadata": {},
   "source": [
    "    2.1.3 打包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "peripheral-digit",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'oto': 公司名称                        某公司\n",
       "  请款部门                        财务部\n",
       "  填单日期        2022-03-29 00:00:00\n",
       "  小写合计                       4866\n",
       "  大写合计                  肆仟捌佰陆拾陆元整\n",
       "  附件数量                          7\n",
       "  是转工资                          √\n",
       "  否转工资                        NaN\n",
       "  原借支                        4000\n",
       "  现报销                         NaN\n",
       "  剩余交回                        NaN\n",
       "  收款账号    2220 1298 2983 2981 337\n",
       "  开户银行                       建设银行\n",
       "  Name: 1, dtype: object,\n",
       "  'mtm':    ID 事由或品名 单位  数量    单价   币种    金额   备注\n",
       "  0   1   订书机  个   5    10  人民币    50  老板用\n",
       "  1   1   打印机  台   2  2000  人民币  4000  NaN\n",
       "  2   1    鼠标  个  10    75  人民币   750  NaN\n",
       "  3   1   笔记本  本   3    20  人民币    60  NaN\n",
       "  4   1   回形针  盒   3     2  人民币     6  NaN},\n",
       " {'oto': 公司名称                        某公司\n",
       "  请款部门                        财务部\n",
       "  填单日期        2022-03-29 00:00:00\n",
       "  小写合计                         20\n",
       "  大写合计                       贰拾元整\n",
       "  附件数量                          1\n",
       "  是转工资                        NaN\n",
       "  否转工资                          √\n",
       "  原借支                           0\n",
       "  现报销                         NaN\n",
       "  剩余交回                        NaN\n",
       "  收款账号    2220 1298 2983 2981 445\n",
       "  开户银行                       建设银行\n",
       "  Name: 2, dtype: object,\n",
       "  'mtm':    ID 事由或品名 单位  数量  单价   币种  金额   备注\n",
       "  5   2    试验  盒   3   2  人民币   6  NaN\n",
       "  6   2     A  a   1   1  人民币   1  NaN\n",
       "  7   2     B  b   2   2  人民币   4  NaN\n",
       "  8   2     C  c   3   3  人民币   9  NaN}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fil_com = []\n",
    "for idx,adata in adatas.iterrows():\n",
    "    datas = datass[datass['ID']==idx]\n",
    "    combine = {'oto': adata, 'mtm': datas}\n",
    "    fil_com.append(combine)\n",
    "fil_com"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fitted-minnesota",
   "metadata": {},
   "source": [
    "    2.2 创建 Exsheet 对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "immediate-pledge",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "exs = fp.Exsheet(\n",
    "    gap_between_blocks_in_a_page=2,\n",
    "    number_of_blocks_per_page=3\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "friendly-transparency",
   "metadata": {},
   "source": [
    "    2.3 读入模板"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "correct-establishment",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<fillpat.Exblock at 0x26b76a98820>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exs.read_pattern(pat)\n",
    "exs.pattern"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "expensive-idaho",
   "metadata": {},
   "source": [
    "    2.4 准备空白 Excel 工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "palestinian-austria",
   "metadata": {},
   "outputs": [],
   "source": [
    "wb = Workbook()\n",
    "ws = wb.active"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "leading-chrome",
   "metadata": {},
   "source": [
    "    2.5 在工作表中逐一放入模板块"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "nominated-affiliate",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,2):\n",
    "    # 2.5.1 拆包\n",
    "    adata = fil_com[i]['oto']\n",
    "    datas = fil_com[i]['mtm']\n",
    "    # 2.5.2 新块\n",
    "    exs.Id = i + 1\n",
    "    # 2.5.3 填充\n",
    "    exs.fill_blank(oto_loc, adata, mtm_loc, datas)\n",
    "    # 2.5.4  放入 Sheet\n",
    "    exs.put_in_sheet(ws)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "talented-bidder",
   "metadata": {},
   "source": [
    "    2.6 对 Excel 的 Sheet 进行布局设置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "pending-technique",
   "metadata": {},
   "outputs": [],
   "source": [
    "exs.page_layout(ws, margins=exs.NARROWER,scale=85 ,verticalcentered=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acquired-reduction",
   "metadata": {},
   "source": [
    "    2.7 保存为 Excel 文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "temporal-romance",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "done\n"
     ]
    }
   ],
   "source": [
    "wb.save('示例结果.xlsx')\n",
    "print('done')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
